using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class BudgetsDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procBudgets_add]";
        private string _strSPInsertName1 = "dbo.[procBudgets1_add]";
        private string _strSPUpdateName = "dbo.[procBudgets_update]";
        private string _strSPUpdateName1 = "dbo.[procBudgets1_update]";
        private string _strSPDeleteName = "dbo.[procBudgets_delete]";
        private string _strSPDeleteName1 = "dbo.[procBudgets1_delete]";
        private string _strSPGetName = "dbo.[procBudgets_get]";
        private string _strSPGetAllName = "dbo.[procBudgets_getall]";
        private string _strSPGetAllName1 = "dbo.[procBudgets1_getall]";
        private string _strSPGettotal = "dbo.[procBudgets_GetTotal]";
        private string _strSPGettotal1 = "dbo.[procBudgets_GetTotal1]";
        private string _strSPGetPages = "dbo.[procBudgets_getpaged]";
        private string _strSPIsExist = "dbo.[procBudgets_isexist]";
        private string _strSPIsExist1 = "dbo.[procBudgets1_isexist]";
        private string _strTableName = "Budgets";
        private string _strSPGetCondName = "dbo.[procBudgets_getCond]";
        private string _strSPGetCondName1 = "dbo.[procBudgets1_getCond]";
        private string _strSPGetAllbgGroup = "dbo.[procBudgets_getallbgGroup]";
        private string _strSPGetAllbgGroup1 = "dbo.[procBudgets_getallbgGroup1]";
        private string _strSPUpdateShared = "dbo.[procBudgets_updateShared]";
        private string _strSPCheckSharedGroup = "dbo.[procBudgets_CheckSharedGroup]";
        private string _strSPCheckSharedGroup1 = "dbo.[procBudgets_CheckSharedGroup1]";
        private string _strSPgetSumAmt = "dbo.[procBudgets_getSumAmt]";
        private string _strSPgetSumAmt1 = "dbo.[procBudgets1_getSumAmt]";
        private string _strSPgetBudgets = "dbo.[procBudgets_getBudgets]";
        private string _strSPgetBudgets1 = "dbo.[procBudgets1_getBudgets]";
        #endregion Local Variable

        #region Method
        public BudgetsInfo Get_Budgets(
            String Contract,
            Int32 aYear
        , ref string sErr)
        {
            BudgetsInfo objEntr = new BudgetsInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0) objEntr = new BudgetsInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }



        public DataTable GetAll_Budgets(ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT  cast(aYear as nvarchar(10)) +'-'+ Contract AS aKey, Contract AS BudgetCode, aYear AS Year, BudgetGroup AS [Group], LimitAmt, CummulativeAmt AS Requisition, RemainingAmt AS Remaining, 
                      SharedBudget AS isShared, Department AS Dep, ContractHolder AS Holder, Employee AS Emp, [Desc] AS Description, LimitAmtV1 AS ValueV1, Date1 AS DateV1, 
                      LimitAmtV2 AS ValueV2, Date2 AS DateV2, LimitAmtV3 AS ValueV3, Date3 AS DateV3, LimitAmtV4 AS ValueV4, Date4 AS DateV4, LimitAmtV5 AS ValueV5, 
                      Date5 AS DateV5, Desc1V1 AS Note1V1, Desc2V1 AS Note2V1, Desc1V2 AS Note1V2, Desc2V2 AS Note2V2, Desc1V3 AS Note1V3, Desc2V3 AS Note2V3, 
                      Desc1V4 AS Note1V4, Desc2V4 AS Note2V4, Desc1V5 AS Note1V5, Desc2V5 AS Note2V5, Contract + (CASE WHEN isnull([Desc], '') = '' THEN '' ELSE ' - ' + [Desc] END) 
                      AS CodeName
                FROM         Budgets", CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetAll_Budgets1(ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT  cast(aYear as nvarchar(10)) +'-'+ Contract AS aKey, Contract AS BudgetCode, aYear AS Year, BudgetGroup AS [Group], LimitAmt, CummulativeAmt AS Requisition, RemainingAmt AS Remaining, 
                      SharedBudget AS isShared, Department AS Dep, ContractHolder AS Holder, Employee AS Emp, [Desc] AS Description, LimitAmtV1 AS ValueV1, Date1 AS DateV1, 
                      LimitAmtV2 AS ValueV2, Date2 AS DateV2, LimitAmtV3 AS ValueV3, Date3 AS DateV3, LimitAmtV4 AS ValueV4, Date4 AS DateV4, LimitAmtV5 AS ValueV5, 
                      Date5 AS DateV5, Desc1V1 AS Note1V1, Desc2V1 AS Note2V1, Desc1V2 AS Note1V2, Desc2V2 AS Note2V2, Desc1V3 AS Note1V3, Desc2V3 AS Note2V3, 
                      Desc1V4 AS Note1V4, Desc2V4 AS Note2V4, Desc1V5 AS Note1V5, Desc2V5 AS Note2V5, Contract + (CASE WHEN isnull([Desc], '') = '' THEN '' ELSE ' - ' + [Desc] END) 
                      AS CodeName
                FROM         Budgets1", CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetAll_bgGroup(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllbgGroup);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetAll_bgGroup1(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllbgGroup1);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_Budgets(BudgetsInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            AddParameter("LimitAmt1", objEntr.LimitAmt1);
            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
            AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
            AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
            AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
            AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
            AddParameter("Desc1V1", objEntr.Desc1V1);
            AddParameter("Desc1V2", objEntr.Desc1V2);
            AddParameter("Desc1V3", objEntr.Desc1V3);
            AddParameter("Desc1V4", objEntr.Desc1V4);
            AddParameter("Desc1V5", objEntr.Desc1V5);
            AddParameter("Desc2V1", objEntr.Desc2V1);
            AddParameter("Desc2V2", objEntr.Desc2V2);
            AddParameter("Desc2V3", objEntr.Desc2V3);
            AddParameter("Desc2V4", objEntr.Desc2V4);
            AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

            AddParameter("aYear", objEntr.aYear);
            AddParameter("BudgetGroup", objEntr.BudgetGroup);
            AddParameter("Department", objEntr.Department);
            AddParameter("Employee", objEntr.Employee);
            AddParameter("SharedBudget", objEntr.SharedBudget);
            AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
            AddParameter("ImpDate", objEntr.ImpDate);
            AddParameter("UpdDate", objEntr.UpdDate);
            AddParameter("sort1", objEntr.sort1);
            AddParameter("sort2", objEntr.sort2);
            AddParameter("CreatedBy", objEntr.ModifiedBy);
            AddParameter("Group1", objEntr.Group1);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
        public Int32 Add_Budgets1(BudgetsInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName1);
            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            AddParameter("LimitAmt1", objEntr.LimitAmt1);
            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
            AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
            AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
            AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
            AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
            AddParameter("Desc1V1", objEntr.Desc1V1);
            AddParameter("Desc1V2", objEntr.Desc1V2);
            AddParameter("Desc1V3", objEntr.Desc1V3);
            AddParameter("Desc1V4", objEntr.Desc1V4);
            AddParameter("Desc1V5", objEntr.Desc1V5);
            AddParameter("Desc2V1", objEntr.Desc2V1);
            AddParameter("Desc2V2", objEntr.Desc2V2);
            AddParameter("Desc2V3", objEntr.Desc2V3);
            AddParameter("Desc2V4", objEntr.Desc2V4);
            AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

            AddParameter("aYear", objEntr.aYear);
            AddParameter("BudgetGroup", objEntr.BudgetGroup);
            AddParameter("Department", objEntr.Department);
            AddParameter("Employee", objEntr.Employee);
            AddParameter("SharedBudget", objEntr.SharedBudget);
            AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
            AddParameter("ImpDate", objEntr.ImpDate);
            AddParameter("UpdDate", objEntr.UpdDate);
            AddParameter("sort1", objEntr.sort1);
            AddParameter("sort2", objEntr.sort2);
            AddParameter("CreatedBy", objEntr.ModifiedBy);
            AddParameter("Group1", objEntr.Group1);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_Budgets(BudgetsInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            AddParameter("LimitAmt1", objEntr.LimitAmt1);
            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
            AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
            AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
            AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
            AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
            AddParameter("Desc1V1", objEntr.Desc1V1);
            AddParameter("Desc1V2", objEntr.Desc1V2);
            AddParameter("Desc1V3", objEntr.Desc1V3);
            AddParameter("Desc1V4", objEntr.Desc1V4);
            AddParameter("Desc1V5", objEntr.Desc1V5);
            AddParameter("Desc2V1", objEntr.Desc2V1);
            AddParameter("Desc2V2", objEntr.Desc2V2);
            AddParameter("Desc2V3", objEntr.Desc2V3);
            AddParameter("Desc2V4", objEntr.Desc2V4);
            AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

            AddParameter("aYear", objEntr.aYear);
            AddParameter("BudgetGroup", objEntr.BudgetGroup);
            AddParameter("Department", objEntr.Department);
            AddParameter("Employee", objEntr.Employee);
            AddParameter("SharedBudget", objEntr.SharedBudget);
            AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
            AddParameter("ImpDate", objEntr.ImpDate);
            AddParameter("UpdDate", objEntr.UpdDate);
            AddParameter("sort1", objEntr.sort1);
            AddParameter("sort2", objEntr.sort2);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("Group1", objEntr.Group1);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Update_Budgets1(BudgetsInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName1);

            AddParameter("Contract", objEntr.Contract);
            AddParameter("LimitAmt", objEntr.LimitAmt);
            AddParameter("LimitAmt1", objEntr.LimitAmt1);
            AddParameter("CummulativeAmt", objEntr.CummulativeAmt);
            AddParameter("RemainingAmt", objEntr.RemainingAmt);
            AddParameter("ContractHolder", objEntr.ContractHolder);
            AddParameter("Desc", objEntr.Desc);
            AddParameter("LimitAmtV1", objEntr.LimitAmtV1);
            AddParameter("LimitAmtV2", objEntr.LimitAmtV2);
            AddParameter("LimitAmtV3", objEntr.LimitAmtV3);
            AddParameter("LimitAmtV4", objEntr.LimitAmtV4);
            AddParameter("LimitAmtV5", objEntr.LimitAmtV5);
            AddParameter("Desc1V1", objEntr.Desc1V1);
            AddParameter("Desc1V2", objEntr.Desc1V2);
            AddParameter("Desc1V3", objEntr.Desc1V3);
            AddParameter("Desc1V4", objEntr.Desc1V4);
            AddParameter("Desc1V5", objEntr.Desc1V5);
            AddParameter("Desc2V1", objEntr.Desc2V1);
            AddParameter("Desc2V2", objEntr.Desc2V2);
            AddParameter("Desc2V3", objEntr.Desc2V3);
            AddParameter("Desc2V4", objEntr.Desc2V4);
            AddParameter("Desc2V5", objEntr.Desc2V5);
            //AddParameter("Date1", objEntr.Date1);
            if (objEntr.Date1 == DateTime.MinValue)
                AddParameter("Date1", DBNull.Value);
            else
                AddParameter("Date1", objEntr.Date1);
            //AddParameter("Date2", objEntr.Date2);
            if (objEntr.Date2 == DateTime.MinValue)
                AddParameter("Date2", DBNull.Value);
            else
                AddParameter("Date2", objEntr.Date2);
            //AddParameter("Date3", objEntr.Date3);
            if (objEntr.Date3 == DateTime.MinValue)
                AddParameter("Date3", DBNull.Value);
            else
                AddParameter("Date3", objEntr.Date3);
            //AddParameter("Date4", objEntr.Date4);
            if (objEntr.Date4 == DateTime.MinValue)
                AddParameter("Date4", DBNull.Value);
            else
                AddParameter("Date4", objEntr.Date4);
            //AddParameter("Date5", objEntr.Date5);
            if (objEntr.Date5 == DateTime.MinValue)
                AddParameter("Date5", DBNull.Value);
            else
                AddParameter("Date5", objEntr.Date5);

            AddParameter("aYear", objEntr.aYear);
            AddParameter("BudgetGroup", objEntr.BudgetGroup);
            AddParameter("Department", objEntr.Department);
            AddParameter("Employee", objEntr.Employee);
            AddParameter("SharedBudget", objEntr.SharedBudget);
            AddParameter("aYearBudgetGroup", objEntr.aYearBudgetGroup);
            AddParameter("ImpDate", objEntr.ImpDate);
            AddParameter("UpdDate", objEntr.UpdDate);
            AddParameter("sort1", objEntr.sort1);
            AddParameter("sort2", objEntr.sort2);
            AddParameter("ModifiedBy", objEntr.ModifiedBy);
            AddParameter("Group1", objEntr.Group1);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_Budgets(
            String Contract,
            Int32 aYear
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string Delete_Budgets1(
            String Contract,
            Int32 aYear
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName1);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

       

        public Boolean IsExist_Budgets(
            String Contract,
            Int32 aYear
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
        public Boolean IsExist_Budgets1(
            String Contract,
            Int32 aYear
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist1);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(BudgetsInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetCond(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetCond1(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetCondName1);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public string Update_Shared(BudgetsInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateShared);
            AddParameter("Contract", objEntr.Contract);
            AddParameter("aYear", objEntr.aYear);
            AddParameter("SharedBudget", objEntr.SharedBudget);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;

        }

        public Boolean IsExist_GroupOtherShared(
            String Contract,
            Int32 aYear,
            String BudgetGroup,
            Int16 SharedBudget
        )
        {
            InitConnect();
            InitSPCommand(_strSPCheckSharedGroup);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);
            AddParameter("BudgetGroup", BudgetGroup);
            AddParameter("SharedBudget", SharedBudget);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count >= 1)
                return true;
            return false;
        }
        public Boolean IsExist_GroupOtherShared1(
            String Contract,
            Int32 aYear,
            String BudgetGroup,
            Int16 SharedBudget
        )
        {
            InitConnect();
            InitSPCommand(_strSPCheckSharedGroup1);

            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);
            AddParameter("BudgetGroup", BudgetGroup);
            AddParameter("SharedBudget", SharedBudget);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count >= 1)
                return true;
            return false;
        }

        public DataTable GetTotal(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGettotal);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetTotal1(string strCond, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGettotal1);
            AddParameter("Cond", strCond);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public decimal Budgets_getSumAmt(String Contract, Int32 aYear, ref string sErr)
        {
            decimal result = 0;
            openConnection();
            InitSPCommand(_strSPgetSumAmt);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            DataTable list = new DataTable();
            try
            {
                object o = executeScalar();
                if (o != DBNull.Value) result = Convert.ToDecimal(o);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (sErr != "") ErrorLog.SetLog(sErr);

            return result;
        }
        public decimal Budgets_getSumAmt1(String Contract, Int32 aYear, ref string sErr)
        {
            decimal result = 0;
            openConnection();
            InitSPCommand(_strSPgetSumAmt1);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            DataTable list = new DataTable();
            try
            {
                object o = executeScalar();
                if (o != DBNull.Value) result = Convert.ToDecimal(o);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (sErr != "") ErrorLog.SetLog(sErr);

            return result;
        }

        public DataTable Budgets_getBudgets(
            String Contract,
            Int32 aYear, ref string sErr
        )
        {
            InitConnect();
            InitSPCommand(_strSPgetBudgets);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable Budgets_getBudgets1(
            String Contract,
            Int32 aYear, ref string sErr
        )
        {
            InitConnect();
            InitSPCommand(_strSPgetBudgets1);
            AddParameter("Contract", Contract);
            AddParameter("aYear", aYear);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public string updateTotalHL(string code, int year, ref string sErr)
        {
            string sql = @"UPDATE    Budgets
	                SET	CummulativeAmt = (SELECT     SUM(Amt) AS Amount
			                FROM          PurchaseRequisition
			                WHERE      Contract = @Contract AND aYear = @aYear) AS CummulativeAmt,
		                RemainingAmt = LimitAmt - CummulativeAmt
                WHERE     Contract = @Contract AND aYear = @aYear";
            openConnection();

            InitCommand(sql, CommandType.Text);
            AddParameter("Contract", code);
            AddParameter("aYear", year);


            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            return sErr;
        }

        public string updateTotalHV(string code, int year, ref string sErr)
        {
            string sql = @"UPDATE    Budgets
	                SET	CummulativeAmt = (SELECT     SUM(Amt) AS Amount
			                FROM          PurchaseRequisition1
			                WHERE      Contract = @Contract AND aYear = @aYear) AS CummulativeAmt,
		                RemainingAmt = LimitAmt - CummulativeAmt
                WHERE     Contract = @Contract AND aYear = @aYear";
            openConnection();

            InitCommand(sql, CommandType.Text);
            AddParameter("Contract", code);
            AddParameter("aYear", year);


            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            return sErr;
        }
        public decimal getTotalAmtHL(string code, int year, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(LimitAmt) as Amount
                FROM  Budgets
                WHERE Contract =  @Contract AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);
            AddParameter("Year", year);

            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getTotalAmtHV(string code, int year, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"SELECT SUM(LimitAmt) as Amount
                FROM  Budgets1
                WHERE Contract =  @Contract AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);
            AddParameter("Year", year);


            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public DataTable getTotalAmtsHL(string code, int year, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT isnull(SUM(LimitAmt),0) as LimitAmt, isnull(SUM(RemainingAmt),0) as RemainingAmt
                FROM  Budgets
                WHERE Contract =  @Contract AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);
            AddParameter("Year", year);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public DataTable getTotalAmtsHV(string code, int year, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT isnull(SUM(LimitAmt),0) as LimitAmt, isnull(SUM(RemainingAmt),0) as RemainingAmt
                FROM  Budgets1
                WHERE Contract =  @Contract AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);
            AddParameter("Year", year);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return list;
        }

        public BudgetsInfo Get_BudgetsHL(string code, int year, ref string sErr)
        {
            BudgetsInfo objEntr = new BudgetsInfo();
            InitConnect();
            InitCommand(@"SELECT     b.Contract AS BudgetCode, b.aYear AS Year, b.BudgetGroup AS [Group], b.SharedBudget AS isShared, b.LimitAmt, ISNULL(p.Amt, 0) AS Requisition, 
                      ISNULL(b.LimitAmt, 0) - ISNULL(p.Amt, 0) AS Remaining, b.Department AS Dep, b.[Desc] AS Description, b.LimitAmtV1 AS ValueV1, b.Date1 AS DateV1, 
                      b.LimitAmtV2 AS ValueV2, b.Date2 AS DateV2, b.LimitAmtV3 AS ValueV3, b.Date3 AS DateV3, b.LimitAmtV4 AS ValueV4, b.Date4 AS DateV4, b.LimitAmtV5 AS ValueV5, 
                      b.Date5 AS DateV5, b.ContractHolder AS Holder, b.Employee AS Emp, b.Desc1V1 AS Note1V1, b.Desc2V1 AS Note2V1, b.Desc1V2 AS Note1V2, b.Desc2V2 AS Note2V2, 
                      b.Desc1V3 AS Note1V3, b.Desc2V3 AS Note2V3, b.Desc1V4 AS Note1V4, b.Desc2V4 AS Note2V4, b.Desc1V5 AS Note1V5, b.Desc2V5 AS Note2V5, b.sort1, b.sort2, 
                      b.CreatedBy, b.ModifiedBy, b.CreatedDate, b.ModifiedDate, b.LimitAmt1, b.aYearBudgetGroup, b.ImpDate, b.UpdDate, b.Group1
FROM         Budgets AS b LEFT OUTER JOIN
                          (SELECT     SUM(Amt) AS Amt, aYear, Contract
                            FROM          PurchaseRequisition
                            GROUP BY aYear, Contract) AS p ON p.aYear = b.aYear AND p.Contract = b.Contract
WHERE     (@Contract = b.Contract) AND (@aYear = b.aYear)", CommandType.Text);
            AddParameter("Contract", code);
            AddParameter("aYear", year);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (list.Rows.Count > 0)
                objEntr = new BudgetsInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public BudgetsInfo Get_BudgetsHV(string code, int year, ref string sErr)
        {
            BudgetsInfo objEntr = new BudgetsInfo();
            InitConnect();
            InitCommand(@"SELECT     b.Contract AS BudgetCode, b.aYear AS Year, b.BudgetGroup AS [Group], b.SharedBudget AS isShared, b.LimitAmt, ISNULL(p.Amt, 0) AS Requisition, 
                      ISNULL(b.LimitAmt, 0) - ISNULL(p.Amt, 0) AS Remaining, b.Department AS Dep, b.[Desc] AS Description, b.LimitAmtV1 AS ValueV1, b.Date1 AS DateV1, 
                      b.LimitAmtV2 AS ValueV2, b.Date2 AS DateV2, b.LimitAmtV3 AS ValueV3, b.Date3 AS DateV3, b.LimitAmtV4 AS ValueV4, b.Date4 AS DateV4, b.LimitAmtV5 AS ValueV5, 
                      b.Date5 AS DateV5, b.ContractHolder AS Holder, b.Employee AS Emp, b.Desc1V1 AS Note1V1, b.Desc2V1 AS Note2V1, b.Desc1V2 AS Note1V2, b.Desc2V2 AS Note2V2, 
                      b.Desc1V3 AS Note1V3, b.Desc2V3 AS Note2V3, b.Desc1V4 AS Note1V4, b.Desc2V4 AS Note2V4, b.Desc1V5 AS Note1V5, b.Desc2V5 AS Note2V5, b.sort1, b.sort2, 
                      b.CreatedBy, b.ModifiedBy, b.CreatedDate, b.ModifiedDate, b.LimitAmt1, b.aYearBudgetGroup, b.ImpDate, b.UpdDate, b.Group1
FROM         Budgets1 AS b LEFT OUTER JOIN
                          (SELECT     SUM(Amt) AS Amt, aYear, Contract
                            FROM          PurchaseRequisition1
                            GROUP BY aYear, Contract) AS p ON p.aYear = b.aYear AND p.Contract = b.Contract
WHERE     (@Contract = b.Contract) AND (@aYear = b.aYear)", CommandType.Text);
            AddParameter("Contract", code);
            AddParameter("aYear", year);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = new BudgetsInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAllYearHL(int year, ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT     Contract AS BudgetCode, aYear AS Year, BudgetGroup AS [Group], LimitAmt, CummulativeAmt AS Requisition, RemainingAmt AS Remaining, 
                      SharedBudget AS isShared, Department AS Dep, ContractHolder AS Holder, Employee AS Emp, [Desc] AS Description, LimitAmtV1 AS ValueV1, Date1 AS DateV1, 
                      LimitAmtV2 AS ValueV2, Date2 AS DateV2, LimitAmtV3 AS ValueV3, Date3 AS DateV3, LimitAmtV4 AS ValueV4, Date4 AS DateV4, LimitAmtV5 AS ValueV5, 
                      Date5 AS DateV5, Desc1V1 AS Note1V1, Desc2V1 AS Note2V1, Desc1V2 AS Note1V2, Desc2V2 AS Note2V2, Desc1V3 AS Note1V3, Desc2V3 AS Note2V3, 
                      Desc1V4 AS Note1V4, Desc2V4 AS Note2V4, Desc1V5 AS Note1V5, Desc2V5 AS Note2V5, Contract + (CASE WHEN isnull([Desc], '') = '' THEN '' ELSE ' - ' + [Desc] END) 
                      AS CodeName
                FROM         Budgets
                WHERE   aYear = @aYear ", CommandType.Text);

            AddParameter("aYear", year);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetAllYearHV(int year, ref string sErr)
        {
            InitConnect();

            InitCommand(@"SELECT     Contract AS BudgetCode, aYear AS Year, BudgetGroup AS [Group], LimitAmt, CummulativeAmt AS Requisition, RemainingAmt AS Remaining, 
                      SharedBudget AS isShared, Department AS Dep, ContractHolder AS Holder, Employee AS Emp, [Desc] AS Description, LimitAmtV1 AS ValueV1, Date1 AS DateV1, 
                      LimitAmtV2 AS ValueV2, Date2 AS DateV2, LimitAmtV3 AS ValueV3, Date3 AS DateV3, LimitAmtV4 AS ValueV4, Date4 AS DateV4, LimitAmtV5 AS ValueV5, 
                      Date5 AS DateV5, Desc1V1 AS Note1V1, Desc2V1 AS Note2V1, Desc1V2 AS Note1V2, Desc2V2 AS Note2V2, Desc1V3 AS Note1V3, Desc2V3 AS Note2V3, 
                      Desc1V4 AS Note1V4, Desc2V4 AS Note2V4, Desc1V5 AS Note1V5, Desc2V5 AS Note2V5, Contract + (CASE WHEN isnull([Desc], '') = '' THEN '' ELSE ' - ' + [Desc] END) 
                      AS CodeName
                FROM         Budgets1
                WHERE   aYear = @aYear ", CommandType.Text);

            AddParameter("aYear", year);
            DataTable list = new DataTable();

            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public object GetShortAllHL(ref string sErr)
        {
            InitConnect();

            InitCommand(@"SELECT     Contract AS BudgetCode
                FROM         Budgets
                GROUP BY Contract ", CommandType.Text);

            DataTable list = new DataTable();

            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public object GetShortAllHV(ref string sErr)
        {
            InitConnect();

            InitCommand(@"SELECT     Contract AS BudgetCode
                FROM         Budgets1
                GROUP BY Contract ", CommandType.Text);

            DataTable list = new DataTable();

            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public decimal getTotalGroupAmtHL(string code, int year, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"DECLARE @group AS nvarchar(15)
                SET @group = (SELECT BudgetGroup FROM Budgets WHERE Contract = @Contract AND aYear = @Year)

                SELECT SUM(LimitAmt) as Amount
                FROM  Budgets
                WHERE BudgetGroup =  @group AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);
            AddParameter("Year", year);

            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public decimal getTotalGroupAmtHV(string code, int year, ref string sErr)
        {
            decimal ret = 0;
            openConnection();
            string sql = @"DECLARE @group AS nvarchar(15)
                SET @group = (SELECT BudgetGroup FROM Budgets1 WHERE Contract = @Contract AND aYear = @Year)

                SELECT SUM(LimitAmt) as Amount
                FROM  Budgets1
                WHERE BudgetGroup =  @group AND aYear = @Year";

            InitCommand(sql, CommandType.Text);

            AddParameter("Contract", code);
            AddParameter("Year", year);

            try
            {
                object obj = executeScalar();
                if (obj == DBNull.Value) ret = 0;
                else ret = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }
    }
}
